3.07. Процедурные расширения

В РАЗРАБОТКЕНЕ ДЛЯ НОВИЧКОВНЕ ОБЯЗАТЕЛЬНО

Разработчику Аналитику Тестировщику
Архитектору Инженеру

Процедурные расширения – PL/SQL и T-SQL

О процедурных расширениях

Хранимые программы в SQL представляют собой блоки кода, хранящиеся непосредственно в базе данных и выполняемые на сервере. Они реализуются с помощью процедурных расширений языка SQL, таких как PL/SQL (Oracle) и T-SQL (Microsoft SQL Server). Эти языки позволяют использовать переменные, циклы, условные конструкции, обработку исключений и другие элементы императивного программирования, расширяя возможности декларативного SQL.

T-SQL (Transact SQL) это процедурное расширение SQL от Microsoft, используемая в Microsoft SQL Server, Sybase.

PL/SQL – процедурное расширение SQL от Oracle для Oracle Database.

Основная задача – предоставление дополнительных возможностей и инструментов. Вместо того, чтобы углубляться в какой-то один, мы рассмотрим сразу оба в сравнении.

Как оно работает? Давайте рассмотрим на примере T-SQL:

SQL – это стандартный язык запросов, используемый для работы с реляционными базами данных. Расширение же добавляет дополнительные возможности, которые позволяют писать более сложные и гибкие запросы, а также управлять бизнес-логикой непосредственно в базе данных.

PL/SQL использует пакеты:

CREATE OR REPLACE PACKAGE emp_pkg AS
  PROCEDURE hire_employee(...);
  FUNCTION calculate_salary(...) RETURN NUMBER;
END emp_pkg;

CREATE OR REPLACE PACKAGE BODY emp_pkg AS
  -- Реализация
END emp_pkg;

T-SQL не имеет прямого аналога пакетов, но можно использовать группировку процедур по схемам, и сборки (assemblies) в SQL Server.

Особые возможности

Только в PL/SQL	Только в T-SQL
Пакеты (Packages)	Оконные функции (более продвинутые)
Триггеры на схемном уровне	TOP вместо LIMIT
Коллекции (VARRAY, Nested Tables)	TRY/CATCH блоки
Оператор %ROWTYPE и %TYPE	Табличные переменные

Структура блока PL/SQL

Блок PL/SQL — это основная структурная единица кода. Он состоит из нескольких необязательных и обязательных разделов:

[ <<метка>> ]
[ DECLARE
  -- объявления переменных, констант, курсоров, пользовательских типов ]
BEGIN
  -- исполняемые операторы (обязательный раздел)
[ EXCEPTION
  -- обработка исключений ]
END;

• DECLARE — опциональный раздел для объявления переменных, констант, курсоров, подпрограмм и типов данных. Присутствует только в анонимных блоках или внутренних блоках.
• BEGIN ... END — обязательный раздел, содержащий исполняемые операторы.
• EXCEPTION — опциональный раздел для перехвата и обработки исключений (ошибок времени выполнения).

Каждый блок завершается точкой с запятой после END.

Именованные и анонимные блоки

Анонимные блоки — не сохраняются в базе данных, выполняются однократно. Используются для разовых задач.

Пример:

DECLARE
  v_name VARCHAR2(50) := 'Тимур';
BEGIN
  DBMS_OUTPUT.PUT_LINE('Привет, ' || v_name);
END;

Именованные блоки — это хранимые процедуры, функции, пакеты, триггеры. Они компилируются и сохраняются в словаре данных, могут вызываться многократно.

Пример создания процедуры:

CREATE OR REPLACE PROCEDURE greet_user(p_name IN VARCHAR2) IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Привет, ' || p_name);
END;

Переменные и константы

Как и в любом языке программирования, процедурные расширения SQL позволяют объявить переменную, выделив область памяти, куда запишется значение. Для этого используется ключевое слово DECLARE.

Пример на PL/SQL:

DECLARE
  v_name VARCHAR2(100) := 'John';
  v_age NUMBER := 30;
BEGIN
  -- Код
END;

• DECLARE - необязательная секция объявления переменных. Здесь можно определять локальные переменные, которые будут использоваться внутри блока.
• BEGIN - обязательная секция исполняемого кода;
• END; - завершение блока.

В данном случае блок анонимный, то есть не сохраняется в базе как процедура или функция, и выполняется на лету.

• v_name и v_age - это имена переменных;
• VARCHAR2(100) и NUMBER - типы данных;
• := - оператор присваивания;
• 'John' и 30 соответственно, значения.

T-SQL:

DECLARE @name VARCHAR(100) = 'John';
DECLARE @age INT = 30;
BEGIN
  -- Код
END

• DECLARE - ключевое слово для объявления переменной;
• @name и @age - переменные. В T-SQL все переменные начинаются с @ — это обязательное соглашение.
• VARCHAR(100) и INT - типы данных;
• = 'John' и = 30 присваивание значения.

Объявление переменных происходит в разделе DECLARE или в заголовке подпрограммы.

Формат:

идентификатор [CONSTANT] тип_данных [NOT NULL] [:= значение];

Примеры:

v_count NUMBER := 0;
c_app_name CONSTANT VARCHAR2(20) := 'ELMA365';
v_is_active BOOLEAN NOT NULL := TRUE;

Типы данных включают скалярные (VARCHAR2, NUMBER, DATE, BOOLEAN) и составные (записи, коллекции).

Можно использовать атрибуты %TYPE и %ROWTYPE для согласования типов с колонками таблиц:

v_employee_name employees.last_name%TYPE;
v_employee_record employees%ROWTYPE;

Условные команды

Условные ветвления позволяют выполнять определённые действия строго при соответствии установленным условиям. Это классические IF THEN ELSE.

Получается, условное ветвление реализуется через IF и CASE.

IF-THEN-ELSIF-END IF

IF condition1 THEN
  -- действия
ELSIF condition2 THEN
  -- действия
ELSE
  -- действия
END IF;

В PL/SQL IF condition THEN - это начало условного оператора. Проверяется логическое выражение condition — если оно истинно (TRUE), выполняются команды после THEN. Условие должно возвращать логическое значение: TRUE, FALSE или NULL. Если условие NULL — считается как FALSE.

В T-SQL IF condition - начало условного оператора. Проверяется логическое выражение condition — если оно истинно (не NULL и не 0), то выполняется следующий за ним блок. В T-SQL условие может быть любым выражением, возвращающим число или логическое значение. Если результат 0 → FALSE. Если NULL → тоже считается как FALSE. Если 1 (или любое ненулевое число) → TRUE.

Пример:

IF v_salary > 10000 THEN
  v_bonus := 1000;
ELSIF v_salary > 5000 THEN
  v_bonus := 500;
ELSE
  v_bonus := 100;
END IF;

CASE

Поддерживается CASE в двух формах: простом и выражаемом.

CASE grade
  WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Отлично');
  WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Хорошо');
  ELSE DBMS_OUTPUT.PUT_LINE('Нужно улучшить');
END CASE;

Циклы

Циклы позволяют повторять действия снова и снова, пока условие истинно (WHILE, пока) и для каждого элемента в наборе данных (FOR).

В PL/SQL доступны три типа циклов:

1. Простой цикл (LOOP). Выполняется до явного выхода через EXIT или EXIT WHEN.

LOOP
  -- операторы
  EXIT WHEN условие;
END LOOP;

2. Цикл с условием (WHILE):

WHILE условие LOOP
  -- операторы
END LOOP;

WHILE ... LOOP ... END LOOP; - цикл с предусловием. Цикл выполняется пока условие истинно (TRUE). Проверка условия происходит перед каждой итерацией. Если условие изначально FALSE или NULL — цикл не выполнится ни разу. Если забыть изменить переменную внутри цикла — получится бесконечный цикл. Условие может быть любым логическим выражением. В PL/SQL NULL в условии = FALSE.

WHILE ... BEGIN ... END это цикл, который выполняется, пока условие истинно. Проверка условия происходит перед каждой итерацией. Условие может быть любым выражением, возвращающим число или логическое значение.

3. Цикл со счётчиком (FOR). Автоматически управляет счётчиком.

FOR i IN 1..10 LOOP
  DBMS_OUTPUT.PUT_LINE('Итерация: ' || i);
END LOOP;

FOR ... IN ... LOOP ... END LOOP; - цикл с фиксированным числом итераций. Цикл выполняется фиксированное число раз — от начального до конечного значения (включительно). Переменная цикла объявляется автоматически — не нужно её объявлять в DECLARE. Переменная цикла (i) доступна только внутри цикла.

Для обратного порядка:

FOR i IN REVERSE 1..10 LOOP
  -- ...
END LOOP;

В отличие от Oracle (PL/SQL) или PostgreSQL (PL/pgSQL), T-SQL не имеет встроенного цикла FOR. Поэтому для перебора диапазона (например, от 1 до 10) используют WHILE с ручным управлением счётчиком. Переменную цикла нужно объявить и инициализировать вручную (DECLARE @i INT = 1). Можно использовать любые шаги: +2, *2, -1 — в зависимости от задачи.

Обработка исключений

В случае, если логика программы пошла «не по плану», есть возможность обработки ошибок с добавлением сценария. Можно перехватывать разные типы ошибок по отдельности.

Исключения перехватываются в разделе EXCEPTION. Поддерживаются предопределённые и пользовательские исключения.

Пример:

BEGIN
  SELECT salary INTO v_salary FROM employees WHERE id = p_id;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Сотрудник не найден');
    v_salary := 0;
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('Найдено более одной записи');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Неизвестная ошибка');
END;

• BEGIN - начало исполняемой части блока;
• EXCEPTION - начало секции обработки исключений. Эта секция необязательна, но крайне полезна для надёжного кода. Секция EXCEPTION должна идти после BEGIN и перед END. Если ошибки не было — секция EXCEPTION не выполняется.
• WHEN NO_DATA_FOUND THEN это уже обработка конкретного исключения, в данном случае, когда SQL-запрос (например, SELECT ... INTO) не нашёл ни одной строки.
• END; является завершением блока.

Предопределённые исключения включают:

• NO_DATA_FOUND
• TOO_MANY_ROWS
• DUP_VAL_ON_INDEX
• INVALID_NUMBER

Пользовательские исключения объявляются и связываются с кодами ошибок через PRAGMA EXCEPTION_INIT.

T-SQL имеет схожую функциональность, но синтаксически отличается:

• Нет блочной структуры с DECLARE/BEGIN/END в чистом виде; вместо этого используются BEGIN...END блоки внутри хранимых процедур.
• Переменные объявляются через DECLARE @variable TYPE, присвоение — SET или SELECT.
• Условия: IF...ELSE, циклы: WHILE.
• Обработка ошибок — через TRY...CATCH (начиная с SQL Server 2005).
• Хранимые процедуры создаются с CREATE PROCEDURE.

Пример на T-SQL:

DECLARE @counter INT = 1;
WHILE @counter <= 5
BEGIN
    PRINT 'Итерация ' + CAST(@counter AS VARCHAR);
    SET @counter = @counter + 1;
END

Внутри TRY размещается основная логика - SQL-запросы, вызовы хранимых процедур, присваивания, вычисления. Если в этом блоке не возникает ошибок — блок CATCH не выполняется. BEGIN CATCH ... END CATCH это начало и конец блока обработки ошибок. Выполняется только если в блоке TRY произошла ошибка. В данном примере внутри CATCH — вывод информации об ошибке через функции.

Хранимые процедуры

О хранимых процедурах мы говорили ранее. Вот примеры таких процедур в расширениях.

PL/SQL:

CREATE OR REPLACE PROCEDURE update_salary(
  p_emp_id IN NUMBER,
  p_amount IN NUMBER
) AS
BEGIN
  UPDATE employees 
  SET salary = salary + p_amount 
  WHERE emp_id = p_emp_id;
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END;

• CREATE OR REPLACE PROCEDURE update_salary(...) создаёт или заменяет хранимую процедуру с именем update_salary. По соглашению, имя часто начинается с глагола — что она делает.
• CREATE OR REPLACE — если процедура уже существует, она будет перезаписана, а не вызовет ошибку. Это удобно при разработке.
• (p_emp_id IN NUMBER, p_amount IN NUMBER) - объявление параметров процедуры. p_emp_id, p_amount — имена параметров. Префикс p_ означает "parameter" — это соглашение о стиле. IN — параметр только для входа (по умолчанию, можно не писать). Есть ещё OUT (выходной) и IN OUT (вход-выход). NUMBER — тип данных (число — целое или дробное).
• AS (или IS) является началом тела процедуры. В Oracle можно использовать AS или IS — разницы нет. После этого можно было бы объявить локальные переменные, но здесь их нет — сразу идёт BEGIN.
• BEGIN ... END; - исполняемая часть процедуры;
• UPDATE employees SET salary = salary + p_amount WHERE emp_id = p_emp_id; обновляет зарплату сотрудника, прибавляя p_amount к текущей зарплате.
• COMMIT; фиксирует транзакцию, делает изменения постоянными в БД. В Oracle (в отличие от некоторых других СУБД) транзакция не фиксируется автоматически после DML-запросов (INSERT, UPDATE, DELETE) — нужно явно вызывать COMMIT. Здесь COMMIT стоит после UPDATE — значит, если UPDATE выполнился успешно — изменения сохраняются.
• EXCEPTION ... WHEN OTHERS THEN ... - блок обработки ошибок, перехватывает все исключения, которые не были обработаны явно.
• ROLLBACK; откатывает транзакцию, если произошла ошибка, все изменения (в данном случае — UPDATE) отменяются.
• RAISE пробрасывает исключение дальше, вызывающий код (например, приложение или другая процедура) узнает, что произошла ошибка. Без RAISE ошибка была бы «проглочена» — и вызывающая сторона не узнала бы о проблеме.

T-SQL:

CREATE PROCEDURE update_salary
  @emp_id INT,
  @amount DECIMAL(10,2)
AS
BEGIN
  BEGIN TRY
    BEGIN TRANSACTION;
    
    UPDATE employees 
    SET salary = salary + @amount 
    WHERE emp_id = @emp_id;
    
    COMMIT TRANSACTION;
  END TRY
  BEGIN CATCH
    ROLLBACK TRANSACTION;
    THROW;
  END CATCH
END

Этот код выполняет абсолютно ту же логику.

CREATE PROCEDURE update_salary создаёт хранимую процедуру с именем update_salary. В отличие от Oracle, в SQL Server нет OR REPLACE — если процедура уже существует, нужно сначала удалить её:

DROP PROCEDURE IF EXISTS update_salary;
GO
CREATE PROCEDURE update_salary ...

• @emp_id INT, @amount DECIMAL(10,2) - объявление параметров процедуры. В T-SQL все переменные и параметры начинаются с @.
• AS BEGIN ... END - начало тела процедуры. Всё, что между AS и END — исполняемый код.
• BEGIN TRY ... END TRY - начало и конец блока, защищённого от ошибок. Если внутри возникнет ошибка — управление перейдёт в CATCH.
• BEGIN TRANSACTION; COMMIT TRANSACTION; и ROLLBACK TRANSACTION; - соответствующее управление транзакцией.

Функции

PL/SQL:

CREATE OR REPLACE FUNCTION calculate_bonus(
  p_salary IN NUMBER
) RETURN NUMBER AS
  v_bonus NUMBER;
BEGIN
  v_bonus := p_salary * 0.15;
  RETURN v_bonus;
END;

• CREATE OR REPLACE FUNCTION calculate_bonus(...) - создаёт или заменяет функцию с именем calculate_bonus. Если функция уже существует, она будет перезаписана, а не вызовет ошибку.
• p_salary — имя параметра. Префикс p_ означает «parameter» — это соглашение о стиле (не обязательно, но рекомендуется для читаемости).

В PL/SQL каждая функция должна явно указывать тип возвращаемого значения. Поэтому RETURN NUMBER обязательная часть, нужно указывать, какой тип данных возвращает функция — в данном случае NUMBER. Функция должна вернуть значение — иначе будет ошибка при выполнении.

T-SQL:

CREATE FUNCTION calculate_bonus(
  @salary DECIMAL(10,2)
RETURNS DECIMAL(10,2)
AS
BEGIN
  DECLARE @bonus DECIMAL(10,2);
  SET @bonus = @salary * 0.15;
  RETURN @bonus;
END

• CREATE FUNCTION calculate_bonus(...) создаёт скалярную функцию с именем calculate_bonus. В SQL Server нет OR REPLACE — если функция уже существует, нужно сначала удалить её через DROP FUNCTION IF EXISTS.
• @salary — параметр. В T-SQL все переменные и параметры начинаются с @ — это обязательный синтаксис.
• RETURNS DECIMAL(10,2) указывает, какой тип данных возвращает функция — здесь DECIMAL(10,2). Это обязательно — в T-SQL каждая функция должна явно указывать тип возвращаемого значения.

Работа с датами

PL/SQL:

v_date := TO_DATE('2023-01-15', 'YYYY-MM-DD');
v_tomorrow := v_date + 1;

TO_DATE(string, format) - стандартная функция Oracle для преобразования строки в дату. Формат 'YYYY-MM-DD' точно соответствует входной строке — это важно, иначе будет ошибка.

Переменные v_date и v_tomorrow должны быть предварительно объявлены.

В Oracle можно прибавлять число к дате — это означает прибавить N дней. v_date + 1 соответственно дата + 1 день. Дата — это число дней от условной точки отсчёта.

T-SQL:

DECLARE @date DATE = '2023-01-15';
DECLARE @tomorrow DATE = DATEADD(day, 1, @date);

Здесь объявляется переменная @date типа DATE и сразу инициализирует её значением '2023-01-15'. В SQL Server строки в формате 'YYYY-MM-DD' автоматически преобразуются в DATE — если формат корректен. @ — обязательный префикс для переменных в T-SQL. Для прибавления 1 дня к дате используется функция DATEADD.

Нельзя просто писать @date + 1 — это вызовет ошибку (если @date типа DATE). Нужно использовать специальные функции: DATEADD, DATEDIFF, EOMONTH и т.д. Это более строгий и явный подход — меньше «магии», больше контроля.

★ Работа со строками

Приведём примеры конкатенации (объединения имени и фамилии через пробел) и извлечения подстроки (первые 5 символов из строки).

PL/SQL:

v_full_name := v_first_name || ' ' || v_last_name;
v_substr := SUBSTR(v_string, 1, 5);

|| — оператор конкатенации строк в Oracle (и в стандартном SQL). Можно объединять сколько угодно строк: 'a' || 'b' || 'c' → 'abc'. SUBSTR(string, start_position, [length]) - стандартная функция Oracle для извлечения подстроки. Нумерация символов начинается с 1 (не с 0, как в Python или C#). Если length не указан — берётся до конца строки.

T-SQL:

DECLARE @full_name VARCHAR(200) = @first_name + ' ' + @last_name;
DECLARE @substr VARCHAR(5) = SUBSTRING(@string, 1, 5);

В T-SQL конкатенация строк делается через + (в отличие от || в Oracle). @ — обязательный префикс для переменных. Переменные @first_name, @last_name должны быть объявлены ранее или инициализированы в том же выражении.

Возврат результатов

PL/SQL:

OPEN p_result FOR SELECT * FROM employees;

T-SQL:

SELECT * FROM employees; -- Неявный возврат
-- или
RETURN @value; -- Для функций

PL/SQL и T-SQL предоставляют схожие возможности, но с важными синтаксическими различиями. PL/SQL более структурирован и модулен (благодаря пакетам), в то время как T-SQL предлагает более тесную интеграцию с платформой Microsoft и продвинутые аналитические функции.